

//
//  DataBaseSingleton.m
//  SELF 豆瓣
//
//  Created by dllo on 15/11/7.
//  Copyright (c) 2015年 蓝鸥科技. All rights reserved.
//

#import "DataBaseSingleton.h"
#import <sqlite3.h>

@implementation DataBaseSingleton
+(instancetype)shareDataBase
{
    static DataBaseSingleton *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[DataBaseSingleton alloc]init];
    }
    
    return dataBase;
    
}

static sqlite3 *db = nil;

//打开数据库
-(void)openDB
{
    
    if (db != nil) {
        NSLog(@"数据库已经打开了");
        return;
    }
    
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *sqlPath = [sqlFile stringByAppendingPathComponent:@"doubanData.sqlite"];
    NSLog(@"sqlPath = %@",sqlPath);
    
    BOOL result = sqlite3_open(sqlPath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"打开成功");
    }else
    {
        
        NSLog(@"打开失败");
        
    }
    
    
}
//--------------------------电影表-----------------
//创建电影表
-(void)createMovieTable
{
    // TEXT代表字符串
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS movielist(number INTEGER PRIMARY KEY AUTOINCREMENT, movieId TEXT ,movieName TEXT, pic_url TEXT)";
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK)
    {
        NSLog(@"创建表成功");
    }
    
}

//插入一个电影对象
-(void)insertMovieStr:(Movie*)movie
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO movielist(movieId,movieName,pic_url) VALUES('%@','%@','%@')",movie.movieId,movie.movieName,movie.pic_url];
    
    //执行语句
    BOOL  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else
    {
        
        NSLog(@"插入失败");
        
    }
    
    
}


//查询所有电影列表

-(NSMutableArray*)selectMovie
{
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM movielist"];
    // 创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        
    }else
    {
        NSLog(@"查询失败");
    }
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        // 取出第一列数据
        //        const unsigned char r =  sqlite3_column_int(stmt, 0);
        
        // 取出第二列数据  // 点进去看文档确定接收类型
        const unsigned char *movieId =  sqlite3_column_text(stmt, 1);
        
        
        // 取出第三列
        const unsigned char *movieName =  sqlite3_column_text(stmt, 2);
        
        // 四
        const unsigned char *picurl =  sqlite3_column_text(stmt, 3);
        ;
        
        
        
        // 将C语言类型转换成OC类型
        NSString *mid = [NSString stringWithUTF8String:(const char *)movieId];
        // 后面部分需要强转
        NSString *mna = [NSString stringWithUTF8String:(const char *)movieName];
        NSString *pic = [NSString stringWithUTF8String:(const char *)picurl];
        
        
        //创建电影对象
        Movie *movie = [[Movie alloc]init];
        movie.movieId = mid;
        movie.movieName = mna;
        movie.pic_url = pic;
        [array addObject:movie];
    }
    
    return array;
}

//删除整张表格
-(void)dropMovieTbele
{
    NSString *sql = @"DROP TABLE movielist";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除表成功");
    }else
    {
        NSLog(@"删除表失败");
    }
    
    
    
    
    
    
}

//--------------------用户表---------------------

//创建用户表
-(void)createUserTable
{
    
    
    // TEXT代表字符串
    NSString *sql = @"CREATE TABLE IF NOT EXISTS userlist(number INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ,passWord TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK)
    {
        NSLog(@"创建表成功");
    }
    
}

//插入一用户
-(void)insertStudent:(user*)myuser
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO userlist(name,passWord) VALUES('%@','%@')",myuser.userName,myuser.password];
    //执行语句
    
    BOOL  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else
    {
        
        NSLog(@"插入失败");
        
    }
    
    
}

//查询所有用户
-(NSMutableArray*)selectUser

{
    
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM userlist" ];
    
    // 创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    
    // 查询前的准备工作
    // 参数3.限制sql语句长度,-1为不限制
    // 将数据库对象db,sql语句,跟随指针对象stmt关联到一起
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        
    }else
    {
        NSLog(@"查询失败");
    }
    // 循环遍历每一行数据   // stmt为跟随指针,跟随指针指向每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *name =  sqlite3_column_text(stmt, 1);
        
        
        // 取出第三列
        const unsigned char *pass =  sqlite3_column_text(stmt, 2);
        
        
        
        
        
        // 将C语言类型转换成OC类型
        // 后面部分需要强转
        NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
        NSString *passrStr = [NSString stringWithUTF8String:(const char *)pass];
        
        
        // 创建学生对象
        user *myuser = [[user alloc]init];
        myuser.userName = nameStr;
        
        myuser.password = passrStr;
        
        
        // 创建一个数组,位于查询方法最上方↑
        [array addObject:myuser];
        
        
        
    }
    
    return array;
}


//-----------电影收藏表-------------
//创建收藏表
-(void)createShouCangTable
{
    // TEXT代表字符串
    
    NSString *sql = @"CREATE TABLE IF NOT EXISTS mymovielist(number INTEGER PRIMARY KEY AUTOINCREMENT, moviename TEXT ,rating TEXT,score TEXT,person TEXT )";
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK)
    {
        NSLog(@"创建表成功");
    }
    
}

//插入一个电影收藏对象
-(void)insertShouCang:(movieShouCang*)mshou
{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO mymovielist(moviename,rating,score,person) VALUES('%@','%@','%@','%@')",mshou.movieName,mshou.rating,mshou.score,mshou.person];
    
    
    //执行语句
    
    BOOL  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }else
    {
        
        NSLog(@"插入失败");
        
    }
    
    
    
}


//查询所有电影对象
-(NSMutableArray*)selectAllShouCang

{
    
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM mymovielist"];
    
    // 创建查询数据库跟随指针
    sqlite3_stmt *stmt = nil;
    
    // 查询前的准备工作
    // 参数3.限制sql语句长度,-1为不限制
    // 将数据库对象db,sql语句,跟随指针对象stmt关联到一起
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
        
    }else
    {
        NSLog(@"查询失败");
    }
    // 循环遍历每一行数据   // stmt为跟随指针,跟随指针指向每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *name =  sqlite3_column_text(stmt, 1);
        const unsigned char *rat =  sqlite3_column_text(stmt, 2);
        
        const unsigned char *sco =  sqlite3_column_text(stmt, 3);
        const unsigned char *perso =  sqlite3_column_text(stmt, 4);
        
        
        
        // 将C语言类型转换成OC类型
        // 后面部分需要强转
        NSString *moviename = [NSString stringWithUTF8String:(const char *)name];
        NSString *rating = [NSString stringWithUTF8String:(const char *)rat];
        NSString *scor = [NSString stringWithUTF8String:(const char *)sco];
        NSString *person = [NSString stringWithUTF8String:(const char *)perso];
        
        
        // 创建对象
        movieShouCang *moSC = [[movieShouCang alloc]init];
        moSC.movieName = moviename;
        moSC.rating = rating;
        moSC.score = scor;
        moSC.person = person;
        
        // 创建一个数组,位于查询方法最上方↑
        [array addObject:moSC];
        
        
    }
    
    return array;
}


//电影收藏项目删除
-(void)deleteCollectings:(NSString *)moviename name:(NSString *)name{
    NSLog(@"%@",moviename);
    NSLog(@"%@",name);
    
    NSString *sql = [NSString stringWithFormat:@"DELETE  FROM mymovielist WHERE moviename = '%@' AND person = '%@'",moviename,name];
    
    BOOL result =  sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else{
        NSLog(@"删除失败");
    }
    
    
    
}

//删除电影表
-(void)dropMovieShouCangTbele
{
    NSString *sql = @"DROP TABLE mymovielist";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除电影收藏表成功");
    }else
    {
        NSLog(@"删除电影收藏表失败");
    }
    
    
}

@end

